인덱스가 많아질수록 쓰기가 느려지는 이유

목차

• #문제가 되는 상황
• #인덱스 하나는 별도의 쓰기 구조 하나다
• #INSERT 때 추가되는 작업
• #UPDATE는 바뀐 열에 따라 비용이 달라진다
• #DELETE도 모든 인덱스에 흔적을 남긴다
• #페이지 분할과 키 순서의 영향
• #버퍼 풀과 redo log에도 비용이 전파된다
• #유니크 인덱스는 중복 검사 비용도 있다
• #겹치는 인덱스를 찾는 방법
• #사용량이 0인 인덱스도 바로 삭제하면 안 된다
• #안전하게 인덱스를 줄이는 절차
• #읽기와 쓰기를 함께 측정한다
• #결론
• #관련 노트

문제가 되는 상황

느린 쿼리를 만날 때마다 인덱스를 하나씩 추가하다 보면 주문 테이블이 다음처럼 변할 수 있다.

PRIMARY KEY (id)
INDEX ix_orders_user (user_id)
INDEX ix_orders_user_created (user_id, created_at)
INDEX ix_orders_user_created_status (user_id, created_at, status)
INDEX ix_orders_status (status)
INDEX ix_orders_status_created (status, created_at)
INDEX ix_orders_created (created_at)
INDEX ix_orders_deleted (is_deleted)

각 인덱스에는 처음 만들었던 이유가 있다. 그런데 시간이 지나며 쿼리가 사라지고 더 긴 복합 인덱스가 추가되면 일부는 겹치거나 거의 사용되지 않을 수 있다. 읽기 계획에는 후보가 많아졌지만 주문 생성과 상태 변경은 점점 느려지고 인덱스 저장 공간이 테이블 데이터보다 커질 수도 있다.

“인덱스가 많으면 INSERT가 느리다”는 결론만 외우기보다 한 건의 쓰기가 내부적으로 어떤 구조를 바꾸는지 살펴보면 제거할 인덱스를 더 신중하게 고를 수 있다.

인덱스 하나는 별도의 쓰기 구조 하나다

행 하나를 INSERT할 때 DB는 테이블 데이터만 저장하지 않는다. 클러스터드 인덱스와 각 보조 인덱스에 대응하는 엔트리를 모두 유지해야 한다.

INSERT order(id=501, user_id=8421, status=PENDING, created_at=...)

Primary B+Tree
  └─ key: id=501, value: 전체 행

ix_orders_user_created
  └─ key: (8421, created_at, 501)

ix_orders_status_created
  └─ key: (PENDING, created_at, 501)

ix_orders_created
  └─ key: (created_at, 501)

보조 인덱스가 7개라면 한 행을 저장할 때 최대 7개의 별도 B+Tree에도 엔트리를 반영한다. 엔진이 변경을 버퍼링하거나 배치하는 최적화를 하더라도 논리적으로 유지해야 하는 쓰기 양이 사라지는 것은 아니다.

INSERT 때 추가되는 작업

각 인덱스에 새 엔트리를 넣는 과정은 단순 append가 아닐 수 있다.

1. 키가 들어갈 리프 페이지를 탐색한다.
2. 해당 페이지를 메모리로 읽거나 버퍼 풀에서 찾는다.
3. 정렬 순서에 맞는 위치에 엔트리를 추가한다.
4. 페이지가 가득 찼다면 분할하고 상위 경계를 갱신한다.
5. 복구와 복제를 위한 로그를 기록한다.

flowchart LR
    A[행 INSERT] --> B[Primary index 변경]
    A --> C[Secondary index 1 변경]
    A --> D[Secondary index 2 변경]
    A --> E[Secondary index N 변경]
    B --> F[로그·dirty page]
    C --> F
    D --> F
    E --> F

인덱스가 많을수록 서로 다른 리프 페이지가 동시에 dirty page가 된다. 쓰기 버스트가 오면 버퍼 풀이 변경 페이지로 채워지고 백그라운드 flush 압력도 커질 수 있다.

대량 적재에서는 차이가 더 분명하다. 빈 테이블에 먼저 데이터를 넣은 뒤 인덱스를 일괄 생성하는 방식과, 모든 인덱스를 유지한 채 행을 한 건씩 넣는 방식의 비용이 다를 수 있다. 운영 제약과 DDL 영향을 고려해야 하지만 일회성 백필에서는 인덱스 구축 순서가 중요한 이유다.

UPDATE는 바뀐 열에 따라 비용이 달라진다

UPDATE가 모든 인덱스를 항상 같은 방식으로 다시 쓰는 것은 아니다. 인덱스 키에 포함된 열이 바뀌는지가 중요하다.

CREATE INDEX ix_orders_user_created
ON orders(user_id, created_at);

CREATE INDEX ix_orders_status_created
ON orders(status, created_at);

배송 메모만 바꾼다면 두 인덱스 키는 그대로일 수 있다.

UPDATE orders
SET delivery_memo = :memo
WHERE id = :id;

반면 status를 바꾸면 (status, created_at)의 정렬 위치가 달라진다.

UPDATE orders
SET status = 'PAID'
WHERE id = :id;

기존 엔트리 제거: (PENDING, 2026-09-01, id=501)
새 엔트리 추가:   (PAID,    2026-09-01, id=501)

Covering을 위해 status를 여러 인덱스 끝에 반복해서 넣었다면 주문 상태 변경 한 번이 그 인덱스들을 모두 갱신한다.

INDEX (user_id, created_at, status)
INDEX (shop_id, created_at, status)
INDEX (campaign_id, created_at, status)

읽기 lookup을 줄인 대가로 상태 전이의 write amplification이 커진다. 자주 변경되는 열을 covering payload로 추가할 때 특히 측정이 필요하다.

기본 키 변경은 더 비쌀 수 있다. InnoDB 보조 인덱스가 기본 키 값을 행 포인터로 포함하므로 기본 키가 바뀌면 모든 보조 인덱스 엔트리도 영향을 받을 수 있다. 그래서 기본 키는 짧고 안정적인 값을 선호한다.

DELETE도 모든 인덱스에 흔적을 남긴다

행을 삭제하면 해당 행이 존재하던 모든 인덱스에서 엔트리를 제거해야 한다.

DELETE FROM orders
WHERE id = :id;

MVCC 엔진은 동시 트랜잭션이 과거 버전을 볼 수 있어야 하므로 즉시 물리 공간을 완전히 회수하지 않고 삭제 표시와 purge 과정을 거칠 수 있다. 인덱스가 많으면 정리해야 할 엔트리와 로그 양도 늘어난다.

대량 삭제 후 파일 크기가 즉시 줄지 않는 이유도 이런 저장 구조와 페이지 재사용 정책에 연결된다. 물리 공간 회수 작업은 별도 테이블 재구성이나 유지보수가 필요할 수 있고, 그 자체가 큰 운영 작업이다.

논리 삭제는 물리 DELETE를 피하지만 비용이 없어지는 것은 아니다.

UPDATE orders
SET is_deleted = true,
    deleted_at = CURRENT_TIMESTAMP
WHERE id = :id;

is_deleted, deleted_at이 여러 인덱스에 있다면 UPDATE가 해당 엔트리를 갱신한다. 활성 행 조회 인덱스를 설계할 때 논리 삭제 열을 반복해서 포함시키는 비용을 함께 본다.

페이지 분할과 키 순서의 영향

B+Tree 리프 페이지에 여유가 없는데 중간 위치로 새 키가 들어오면 페이지를 나눠야 할 수 있다.

Before
Leaf A [10, 20, 30, 40]

Insert 25

After
Leaf A [10, 20, 25] ⇄ Leaf B [30, 40]

페이지 분할은 새 페이지 할당, 엔트리 이동, 부모 페이지 경계 갱신을 만들고 저장 공간 파편화에도 영향을 줄 수 있다. 단조 증가하는 키는 오른쪽 끝에 집중적으로 추가되어 중간 삽입보다 지역성이 좋을 수 있지만, 동시에 많은 세션이 같은 마지막 페이지를 갱신하면 hot page 경합이 생길 수도 있다.

무작위 키는 삽입 위치를 분산하지만 버퍼 풀의 다양한 페이지를 건드리고 페이지 분할 패턴을 바꾼다. UUID 버전과 기본 키 선택을 단순히 “정렬된다/안 된다”로만 판단하지 않고 쓰기 동시성, 키 크기, 보조 인덱스 크기까지 함께 보는 이유다.

버퍼 풀과 redo log에도 비용이 전파된다

인덱스가 커지면 저장 공간만 늘어나는 것이 아니다.

버퍼 풀

자주 쓰는 데이터와 인덱스 페이지가 같은 메모리 공간을 경쟁한다. 사용되지 않는 큰 인덱스의 페이지가 유입되면 유용한 데이터 페이지가 밀려나 캐시 적중률이 낮아질 수 있다.

redo와 undo

장애 복구를 위해 인덱스 변경도 redo에 반영되어야 한다. 트랜잭션 롤백과 MVCC를 위한 undo 관련 작업도 늘 수 있다. 로그 생성량이 커지면 flush와 체크포인트, 복구 시간에 영향을 준다.

복제와 백업

논리 복제라면 원본 변경 이벤트가 중심이지만 복제 대상에서도 같은 인덱스를 갱신해야 한다. 물리 백업과 스냅샷은 큰 인덱스 파일을 포함해 저장·전송 시간이 늘어난다. 새 인덱스 생성 DDL 자체가 복제 지연과 추가 공간을 만들 수도 있다.

인덱스 추가
→ 더 많은 dirty page와 로그
→ flush I/O 증가
→ 복제 적용 작업 증가
→ 백업·복구 데이터 증가

따라서 쓰기 latency만이 아니라 redo 생성률, buffer pool, replica lag, 백업 시간까지 변화를 관찰해야 한다.

유니크 인덱스는 중복 검사 비용도 있다

유니크 인덱스는 새 키를 넣기 전에 같은 키가 존재하는지 확인해야 한다.

CREATE UNIQUE INDEX uq_users_normalized_email
ON users(normalized_email);

이 확인은 비용이지만 비즈니스 규칙을 동시성 안전하게 지키는 중요한 역할이다. 따라서 단순 성능 이유로 유니크 인덱스를 제거해서는 안 된다. 일반 인덱스와 달리 데이터 무결성 보장이 사라진다.

중복된 것처럼 보이는 다음 두 인덱스도 역할이 다를 수 있다.

UNIQUE (provider, normalized_email)
INDEX  (provider, normalized_email, created_at)

긴 인덱스가 짧은 접두를 포함하더라도 유니크 제약의 의미를 자동으로 대체하지 않는다. 유일성 범위가 같고 DB가 긴 인덱스로 동일한 제약을 선언할 수 있는지 별도로 판단해야 한다. 관련 무결성 역할은 Unique Constraint를 비즈니스 규칙으로 활용하기에서 다뤘다.

겹치는 인덱스를 찾는 방법

다음 인덱스들은 접두가 겹친다.

INDEX ix_a (user_id)
INDEX ix_b (user_id, created_at)
INDEX ix_c (user_id, created_at, status)

B+Tree의 왼쪽 접두 규칙만 보면 ix_c가 (user_id)와 (user_id, created_at) 조회를 지원할 수 있어 ix_a와 ix_b가 중복 후보가 된다. 하지만 바로 삭제하기 전에 다음 차이를 확인한다.

• 긴 인덱스가 훨씬 커서 단순 user_id 집계에는 더 비싼가?
• ix_b와 ix_c의 정렬 방향이나 collation이 다른가?
• 유니크 여부가 다른가?
• 쿼리가 ix_b만으로 cover되지만 ix_c도 동일하게 cover하는가?
• 옵티마이저가 실제로 어떤 인덱스를 선택하는가?
• 외래 키 지원에 필요한 인덱스인가?

열 집합이 같아도 순서가 다르면 다른 접근 패턴이다.

INDEX (user_id, status)
INDEX (status, user_id)

첫 번째는 user별 조회, 두 번째는 전체 status별 배치에 쓰일 수 있다. 중복 판단은 이름과 열 개수보다 실제 쿼리의 접두 조건과 실행 계획으로 내려야 한다.

사용량이 0인 인덱스도 바로 삭제하면 안 된다

성능 스키마나 모니터링에서 사용 횟수가 0인 인덱스는 좋은 후보지만 다음 가능성을 확인해야 한다.

• 월말·분기말에만 실행되는 정산 쿼리
• 장애 복구와 관리자 도구
• 통계 수집을 시작하기 전 사용 기록이 초기화됨
• standby나 다른 애플리케이션에서만 실행되는 쿼리
• 유니크 또는 외래 키 제약을 위한 인덱스
• 옵티마이저가 현재 통계 때문에 우연히 선택하지 않은 상태

SELECT object_schema,
       object_name,
       index_name,
       count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'app';

위 조회는 출발점이지 삭제 명령 목록이 아니다. 모니터링 수집 기간과 서버 재시작 시점, 모든 트래픽 주기를 함께 기록해야 한다.

안전하게 인덱스를 줄이는 절차

인덱스 삭제는 쓰기 성능을 개선할 수 있지만, 필요한 읽기 경로를 잃으면 대형 테이블 전체 스캔을 만들 수 있다. 다음처럼 단계적으로 진행한다.

1. 스키마에서 접두가 겹치거나 사용량이 낮은 후보를 찾는다.
2. 해당 인덱스를 사용하는 현재·배치·관리 쿼리를 수집한다.
3. 대체 인덱스로 실행 계획과 실제 시간이 유지되는지 검증한다.
4. 충분한 트래픽 주기 동안 사용량을 관찰한다.
5. 지원 환경이라면 invisible index로 옵티마이저에서 먼저 제외한다.
6. 응답 시간, 전체 스캔, DB 부하, 느린 쿼리 변화를 관찰한다.
7. 문제없을 때 DDL 영향과 롤백 계획을 마련해 제거한다.

-- 지원 버전의 개념 예시
ALTER TABLE orders
ALTER INDEX ix_orders_user INVISIBLE;

DROP INDEX ix_orders_user ON orders;

롤백을 위해 원래 CREATE INDEX DDL을 정확히 보관한다. 삭제 후 다시 만드는 작업은 대형 테이블에서 오래 걸리고 잠금·추가 공간·복제 지연을 만들 수 있으므로 “필요하면 바로 복구”가 실제로 가능한지 미리 계산해야 한다.

읽기와 쓰기를 함께 측정한다

인덱스 변경 전후에 최소한 다음 워크로드를 비교한다.

-- 대표 읽기
EXPLAIN ANALYZE
SELECT id, status, created_at
FROM orders
WHERE user_id = :user_id
ORDER BY created_at DESC
LIMIT 20;

-- 대표 쓰기
INSERT INTO orders(id, user_id, status, created_at, total_amount)
VALUES (:id, :user_id, 'PENDING', CURRENT_TIMESTAMP, :amount);

-- 자주 바뀌는 인덱스 열 UPDATE
UPDATE orders
SET status = 'PAID'
WHERE id = :id;

단일 SQL 마이크로벤치마크만으로 결론내리지 않는다. 인덱스 제거로 INSERT가 빨라져도 특정 목록 API의 p99가 악화될 수 있고, 반대로 핵심 목록의 lookup을 줄이는 covering 인덱스는 쓰기 비용을 감수할 가치가 있을 수 있다. 업무 중요도와 호출량을 가중해 판단한다.

결론

인덱스가 많아질수록 쓰기가 느려지는 이유는 한 행의 변경이 모든 관련 B+Tree, 페이지, 로그와 캐시 상태에 반복해서 반영되기 때문이다. INSERT는 각 인덱스에 엔트리를 추가하고, 인덱스 열 UPDATE는 키 위치를 옮기며, DELETE는 모든 구조의 엔트리를 정리한다. 넓고 자주 변경되는 covering 열과 긴 기본 키는 이 비용을 더 키울 수 있다.

그렇다고 인덱스 수를 적게 유지하는 것 자체가 목표는 아니다. 중요한 읽기를 명확히 지원하는 최소한의 인덱스 집합이 목표다. 접두가 겹치고 사용량이 낮은 후보를 찾되 유니크·외래 키 역할과 드문 배치 쿼리를 확인하고, 가능하면 invisible 상태에서 영향을 관찰한 뒤 제거한다. 최종 결정은 읽기 쿼리 하나의 개선이나 INSERT 하나의 속도가 아니라 전체 워크로드의 응답 시간, 쓰기 증폭, 저장 공간과 운영 비용을 함께 측정해 내려야 한다.

관련 노트

• B+Tree 인덱스가 검색 범위를 줄이는 원리
• 복합 인덱스의 왼쪽 접두 규칙
• Covering Index로 테이블 접근 줄이기
• 인덱스 선택도가 성능에 미치는 영향
• MySQL 복합 인덱스의 순서와 중복 인덱스 판단하기
• Unique Constraint를 비즈니스 규칙으로 활용하기